﻿using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace System.Threading.Tasks
{
    internal partial class NPOIHelper
    {
        /// <summary>
        /// 通过文件路径,创建工作蒲
        /// </summary>
        /// <param name="filePath">excel路径</param>
        /// <returns></returns>
        public static IWorkbook CreateWorkbook(string filePath)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(filePath) || !File.Exists(filePath)) return null;
                using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    IWorkbook workbook = WorkbookFactory.Create(fs);
                    return workbook;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        /// <summary>
        /// Datable导出成Excel
        /// </summary>
        /// <param name="dataTable">数据表</param>
        /// <param name="filePath">导出路径(包括文件名与扩展名)</param>
        public static void DataTableToExcel(DataTable dataTable, string filePath)
        {
            IWorkbook workbook = null;
            try
            {
                if (dataTable == null || dataTable.Rows == null || dataTable.Rows.Count < 1 || !File.Exists(filePath)) return;
                workbook = CreateWorkbook(filePath);
                string sheetName = !string.IsNullOrEmpty(dataTable.TableName) ? dataTable.TableName : "Sheet1";
                List<string> sheetNames = new List<string>();
                ISheet sheet = null;
                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    string tempSheetName = workbook.GetSheetName(i);
                    if (tempSheetName.Equals(sheetName))
                    {
                        var tempSheet = workbook.GetSheet(tempSheetName);
                        if (tempSheet.LastRowNum == 0)
                        {
                            sheet = tempSheet;
                            break;
                        }
                    }
                    sheetNames.Add(workbook.GetSheetName(i));
                }
                if (sheet == null)
                {
                    if (sheetNames.Contains(sheetName)) sheetName = "Sheet" + (sheetNames.Count + 1);
                    sheet = workbook.CreateSheet(sheetName);
                }

                //表头
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dataTable.Columns[i].ColumnName);
                }

                //数据
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.SetCellValue(dataTable.Rows[i][j].ToString());
                    }
                }

                //转为字节数组  
                using (MemoryStream stream = new MemoryStream())
                {
                    using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
                    {
                        workbook.Write(stream);
                        var buf = stream.ToArray();
                        fs.Write(buf, 0, buf.Length);
                        fs.Flush();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                workbook?.Close();
            }
        }
    }
}
